from django.shortcuts import render,redirect
from django.views import View
from django.http import JsonResponse,HttpResponse,request
from django.views.decorators.csrf import csrf_exempt
from django.utils.decorators import method_decorator
from rest_framework.views import APIView,exceptions
from rest_framework.response import Response
from rest_framework.versioning import URLPathVersioning
from rest_framework.parsers import JSONParser,FormParser
from rest_framework import serializers
import hashlib,time,json,configparser
from aaa import models
from aaa.service.init_permission import init_permission
from django.core.cache import cache
from aaa.views.rights_list import all_rights
from django.conf import settings
from aaa.utils import commons,response_code,conn_mysql
from cx_Oracle import Connection
from sshtunnel import SSHTunnelForwarder
from aaa.check import check_url,yzd,edu,medical,cp_file
from rest_framework_jwt.settings import api_settings
from django.db.models import Q
import datetime,os

def md5(user):
    m = hashlib.md5(user.encode('utf-8'))
    ctime = str(time.time())
    m.update(ctime.encode('utf-8'))
    return m.hexdigest()

visit_record = {}
class visitedThrottle(APIView):
    '''60s内只能访问3次'''

    def __init__(self):
        self.history = None

    def allow_request(self,request,view):
        remote_addr = request.META.get('REMOTE_ADDR')
        ctime = time.time()
        #第一次访问写入记录返回True
        if 'remote_addr' not in visit_record:
            visit_record['remote_addr'] = [ctime,]
            print(visit_record)
            return True
        history = visit_record.get('remote_addr')
        self.history = history
        while history and history[-1] < ctime - 60:
            history.pop()
        if len(history) < 3:
            history.insert(0,ctime)
            print('--> history', history)
            return True

    def wait(self):
        ctime = time.time()
        return 60 - (ctime - self.history[-1])

class authView(APIView):
    '''用于用户认证'''

    authentication_classes = []
    permission_classes = []
    # throttle_classes = [visitedThrottle,]
    def post(self,request,*args,**kwargs):
        ret = {'meta':{'status':200,'msg':None}}
        try:
            user = request._request.POST.get('username')
            pwd = request._request.POST.get('password')
            print(request._request.COOKIES)
            print(request._request.session)
            obj = models.UserInfo.objects.filter(username=user,password=pwd).first()
            print('obj',obj)
            if not obj:
                ret['meta']['status'] = 201
                ret['meta']['msg'] = '用户或密码错误'
                # raise exceptions.AuthenticationFailed('用户或密码错误')
            token = md5(user)
            print('auth token',token)
            models.UserToken.objects.update_or_create(user=obj,defaults={'token':token})
            ret['data'] = {'token':token}
            print(ret)
        except Exception as e:
            print('111 e',e)
            ret['meta']['status'] = 201
            ret['meta']['msg'] = e
        return JsonResponse(ret)

order_dict = {
    1:{
        'name':'tom',
        'age':'one'
    },
    2: {
        'name': 'wangwang',
        'age': 'two'
    }
}
class orderView(APIView):
    '''订单相关业务'''
    def get(self,request,*args,**kwargs):
        try:
            print('order request session', request.session.get(settings.MENU_SESSION_KEY))
            ret = {'meta': {'status': 200, 'msg': None}}
            # if request.user.user_type != 0:
            #     ret['meta']['msg'] = '无权访问'
            #     ret['meta']['status'] = 201
            #     return JsonResponse(ret)
            ret['data'] = order_dict

        except Exception as e:
            pass
        return JsonResponse(ret)

class removeRightView(APIView):
    def delete(self,request,*args,**kwargs):
        try:
            permission_list= cache.get('permission_list')
            print('delete permission_list', permission_list)
            roleId = int(kwargs.get('roleId'))
            rightId = kwargs.get('rightId')

            role_obj = models.Role.objects.get(id=roleId)
            right_obj = models.Permission.objects.get(id=rightId)
            role_obj.permissions.remove(right_obj)

            ret = {'meta':{'status':200}}
            role_dict = all_rights()
            print('delete role_dict',role_dict[roleId])
            ret['data'] = role_dict[roleId]
        except Exception as e:
            print('delete e',e)
        return JsonResponse(ret)

class allotRightsView(APIView):
    def post(self,request,*args,**kwargs):
        selected_rids = json.loads(request.body.decode('utf-8)'))
        rids = list(map(lambda i:int(i),selected_rids.get('rids').split(',')))
        print(rids)
        roleId = int(kwargs.get('roleId'))
        role_obj = models.Role.objects.get(id=roleId)
        print('allot role_obj',role_obj)
        role_obj.permissions.set(rids)
        ret = {'meta':{'msg':'更新成功','status':200}}
        return JsonResponse(ret)

class allotRoleView(APIView):
    def put(self,request,*args,**kwargs):
        selected_rid = json.loads(request.body.decode('utf-8)'))
        rid = selected_rid.get('rid')
        print('allotRoleView kwargs',kwargs)
        userId = int(kwargs.get('userId'))
        user_obj = models.UserInfo.objects.get(id=userId)
        user_obj.roles.clear()
        user_obj.roles.add(rid)
        ret = {'meta':{'msg':'更新成功','status':200}}
        return JsonResponse(ret)

class menuView(APIView):
    '''左侧菜单列表'''
    authentication_classes = []
    def get(self,request,*args,**kwargs):
        try:
            # menu_dict = request.session.get(settings.MENU_SESSION_KEY)
            menu_dict = cache.get('menu_dict')
            print('xxx',menu_dict)
            ret = {'meta': {'status': 200, 'msg': None}}
            # if request.user.user_type != 0:
            #     ret['meta']['msg'] = '无权访问'
            #     ret['meta']['status'] = 201
            #     return JsonResponse(ret)
            ret['data'] = menu_dict

        except Exception as e:
            pass
        return JsonResponse(ret)

# class getRolesView(APIView):
#     '''渲染权限菜单'''
#     def get(self,request,*args,**kwargs):
#         ret = {'meta': {'status': 200, 'msg': None}}
#         x = models.Role.objects.filter(permissions__isnull=False).values('id', 'roleName', 'roleDesc',
#                                                                          'permissions__id',
#                                                                          'permissions__authName',
#                                                                          'permissions__url',
#                                                                          'permissions__level').distinct()
#         rights_dict = {}
#         for item in x:
#             print(item)
#             node = {'id':item['permissions__id'], 'authName':item['permissions__authName'],'url':item['permissions__url']}
#             if item['id'] in rights_dict:
#                 rights_dict[item['id']]['children'].append(node)
#             else:
#                 rights_dict[item['id']] = {'id': item['id'], 'roleName': item['roleName'], 'roleDesc': item['roleDesc'],
#                                            'children': [node]}
#         print('rights_dict',rights_dict)
#         rights_list = [item for item in rights_dict.values()]
#         ret['data'] = rights_list
#         return JsonResponse(ret)

class getRolesView(APIView):
    '''权限列表'''
    def get(self,request,*args,**kwargs):
        from aaa.views.rights_list import all_rights
        role_dict = all_rights()
        ret = {'meta': {'status': 200, 'msg': None}}
        f = []
        for i in role_dict:
            f.append(role_dict[i])
        ret['data'] = f
        ret['total'] = len(f)
        return JsonResponse(ret)

class getCategoriesView(APIView):
    '''权限列表'''
    def get(self,request,*args,**kwargs):
        from aaa.views.categories_list import all_categories
        cate_type = request._request.GET.get('type')
        if cate_type:
            cate_type = int(cate_type)
        # print('getCategoriesView',type(cate_type))
        categories_dict = all_categories(cate_type)
        print('categories_dict',categories_dict)
        ret = {'meta': {'status': 200, 'msg': None}}
        f = []
        for i in categories_dict:
            f.append(categories_dict[i])
        ret['data'] = f
        print('f', f)
        return JsonResponse(ret)

class rightsTreeView(APIView):
    '''获取树形权限菜单'''
    def get(self,request,*args,**kwargs):
        print(11111111111111111111111111111111111111)
        from aaa.views.rights_tree import get_rights_tree
        rights_tree_list = get_rights_tree()
        print('rights_tree_list', rights_tree_list)
        ret = {'meta': {'status': 200, 'msg': None}}
        ret['data'] = rights_tree_list
        return JsonResponse(ret)

class orderView(APIView):
    '''订单相关业务'''
    def get(self,request,*args,**kwargs):
        try:
            print('order request session', request.session.get(settings.MENU_SESSION_KEY))
            ret = {'meta': {'status': 200, 'msg': None}}
            # if request.user.user_type != 0:
            #     ret['meta']['msg'] = '无权访问'
            #     ret['meta']['status'] = 201
            #     return JsonResponse(ret)
            ret['data'] = order_dict

        except Exception as e:
            pass
        return JsonResponse(ret)
# class userInfoSerializer(serializers.Serializer):
#     xxx = serializers.CharField(source='get_user_type_display')
#     username = serializers.CharField()
#     password = serializers.CharField()
#     gp = serializers.CharField(source='group.title')
#     rls = serializers.SerializerMethodField()
#     def get_rls(self,row):
#         role_obj_list = row.roles.all()
#         ret = []
#         for item in role_obj_list:
#             ret.append({'id':item.id,'title':item.title})
#         return ret

class userInfoSerializer(serializers.ModelSerializer):
    group = serializers.HyperlinkedIdentityField(view_name='gp',lookup_field='group_id',lookup_url_kwarg='pk')
    class Meta:
        model = models.UserInfo
        fields =  ['id','username','group']

class userGroupSerializer(serializers.ModelSerializer):
    class Meta:
        model = models.UserGroup
        fields = '__all__'

from rest_framework.viewsets import GenericViewSet,ModelViewSet
from rest_framework.pagination import PageNumberPagination,LimitOffsetPagination,CursorPagination
# from aaa.utils.pager import PagerSerialiser

class MyLimitOffsetPagination(GenericViewSet):
    default_limit = 2
    limit_query_param = 'limit'
    offset_query_param = 'offset'
    max_limit = None

# class userInfoView(ModelViewSet):
#     queryset = models.UserInfo.objects.all()
#     serializer_class = PagerSerialiser
    # pagination_class = PageNumberPagination

    # def get(self, request, *args, **kwargs):
    #     # 获取数据
    #     roles = models.UserInfo.objects.all()
    #     pg = PageNumberPagination()
    #
    #     # 在数据库中获取分页的数据
    #     xx = pg.paginate_queryset(queryset=roles,request=request,view=self)
    #     print('xx',xx)
    #     # 对数据进行序列化
    #     ser = PagerSerialiser(instance=xx, many=True)
    #     print(ser)
    #     return Response(ser.data)
    #     # return pg.get_paginated_response(ser.data)

    # def post(self,request,*args,**kwargs):
    #     print(request.data)
    #     return HttpResponse('gee')

class userGroupView(APIView):
    def get(self,request,*args,**kwargs):
        pk = kwargs.get('pk')
        group = models.UserGroup.objects.filter(pk=pk).first()
        ser = userGroupSerializer(instance=group,many=False)
        ret = json.dumps(ser.data,ensure_ascii=False)
        return HttpResponse(ret)

class loginView(APIView):
    authentication_classes = []
    def get(self,request,*args,**kwargs):
        return render(request,'logint.html')
        # return JsonResponse({'data':'ok','meta':{'status': 200, 'msg': 'ok'}})

    def post(self, request, *args, **kwargs):
        data = json.loads(request.body.decode('utf-8'))
        # user = request.POST.get('username')
        # pwd = request.POST.get('password')
        print('data',data)
        #微信登录
        if data.get('code'):
            print('loginView code')
            code = data['code']
            userInfo = data['userInfo']
            try:
                nickname = userInfo['nickName']
                avatar = userInfo['avatarUrl']
                sex = userInfo['gender']
                json_data_wx = commons.get_openid_by_code(code)
                print('json_data_wx',json_data_wx)
                # {'openid':'xxxx', 'session_key':'xxx'}
                if 'errcode' in json_data_wx:
                    res = {
                        'errno': response_code.AUTH_ERROR,
                        'statusCode': 200,
                        'errMsg': 'err 111:' + json_data_wx['errmsg']
                    }
                    return JsonResponse(res)
                weixin_openid = json_data_wx['openid']
                try:
                    print('222 111', 222)
                    customerObj = models.Customer.objects.get(weixin_openid=weixin_openid)
                    print('customerObj 111', customerObj)
                    customerObj.customer_update(nickname, avatar, sex)
                except Exception as e:
                    print('customerObj e',e)
                    customerObj = models.Customer().customer_save(nickname, avatar, sex, weixin_openid)
                    print('customerObj',customerObj)
                # token = md5(nickname)
                # 返回token
                jwt_payload_handler = api_settings.JWT_PAYLOAD_HANDLER
                jwt_encode_handler = api_settings.JWT_ENCODE_HANDLER
                payload = jwt_payload_handler(customerObj.user)
                token = jwt_encode_handler(payload)
                print('customerObj token', token)
                userInfo = {
                    'nickname': customerObj.nickname,
                    'avatar': customerObj.avatar,
                    'sex': customerObj.sex
                }
                res = {
                    'errno': response_code.IS_SUCCESS,
                    'statusCode': 200,
                    'errMsg': '成功',
                    'userInfo': userInfo,
                    'token': token
                }
                return JsonResponse(res)
            except Exception as e:
                print('login e',e)
                res = {
                    'errno': response_code.AUTH_ERROR,
                    'statusCode': 200,
                    'errMsg': '登录出错了'
                }
                return JsonResponse(res)
        user = data['username']
        pwd = data['password']
        print('-->login user', user,pwd)
        # if (user == 'alex' or user == 'wusir' or user == 'yuanhao'):
        current_user = models.UserInfo.objects.filter(username=user,password=pwd).first()
        if not current_user:
            return JsonResponse({'data': 'fail', 'meta': {'status': 204, 'msg': '用户或密码错误'}})
        if not current_user.mg_state:
            return JsonResponse({'data': 'fail', 'meta': {'status': 204, 'msg': '用户未激活'}})
        # current_role = current_user.roles.all().first().roleName
        permission_list,menu_dict = init_permission(current_user,request)
        if not permission_list:
            return JsonResponse({'data': 'fail', 'meta': {'status': 204, 'msg': '用户未分配角色与权限'}})
        # return redirect('/api/v1/roles/')
        token = md5(user)
        models.UserToken.objects.update_or_create(user=current_user,defaults={'token':token})
        print('login token',token)
        # ret['data'] = {'token':token}
        return JsonResponse({'data': {'menu_dict':menu_dict,'token':token,'username':current_user.username},'meta':{'status': 200, 'msg': 'ok'}})
        # else:
        #     # return JsonResponse({'code': 1, 'msg': '账号或密码错误'})
        #     return HttpResponse('fail')

# def login(request):
#
#     if(request.method == 'POST'):
#         data = json.loads(request.body.decode('utf-8'))
#         # user = request.POST.get('username')
#         # pwd = request.POST.get('password')
#         # print('user',user)
#         user = data['username']
#         pwd = data['password']
#         if (user == 'alex' or user == 'wusir'):
#             current_user = models.UserInfo.objects.filter(username=user,password=pwd).first()
#             if not current_user:
#                 return JsonResponse({'data': 'fail', 'meta': {'status': 204, 'msg': 'fail'}})
#             if not current_user.mg_state:
#                 return JsonResponse({'data': 'fail', 'meta': {'status': 204, 'msg': '登陆失败'}})
#             permission_list,menu_dict = init_permission(current_user,request)
#             # return redirect('/api/v1/roles/')
#             return JsonResponse({'data':menu_dict,'meta':{'status': 200, 'msg': 'ok'}})
#         else:
#             # return JsonResponse({'code': 1, 'msg': '账号或密码错误'})
#             return HttpResponse('fail')

class export_infomation_excel(APIView):
    def get(self, request, *args, **kwargs):
        commons.FileHandler().load()
        return JsonResponse({'code': 1})

def read_cfg(name):
    cfg = configparser.RawConfigParser()
    cfg.read(settings.CONFIG_PATH, encoding='utf-8')
    print('cfg.items',cfg.items)
    return cfg.items(name)

class get_dbs(APIView):
    def get(self, request, *args, **kwargs):
        dbs = read_cfg('db')
        dbs_list = []
        d = {}
        for k,v in dbs:
            d['label'] = k
            d['value'] = v
            dbs_list.append(d)
            d = {}
        return JsonResponse({'code': 1, 'data': dbs_list})

def get_sql(sqlname):
    data = read_cfg('sql')
    # print('read_cfg data',data)
    print('cfg sqlname',sqlname)
    # print(type(sqlname))
    # for item in data:
    #     print('--> item 1111111111111111111111',item)
    sql = [item[1] for item in data if sqlname == item[0]]
    print('get_sql sql',sql)
    return sql[0]

#把参数转成字典
def foo(**kwargs):
    #对字典中的datetime时间格式数据转为字符串
    if isinstance(kwargs['create_time'],datetime.datetime):
        kwargs['create_time'] = kwargs['create_time'].strftime(('%Y-%m-%d %X'))
        print('-->foo kwargs',type(kwargs['create_time']))
    return kwargs

#主要作用是把从数据库查到的数据转成字典，并给value加上一个key
#如数据库查到的数据，ret = (('农林牧渔', '172.16.1.6', '2.05', '24.59', '/dev/vda1 used: 7.9G nouse: 30G', '19.75', '74.11', '异常', 'Bootlog: OK', '', datetime.datetime(2022, 8, 19, 16, 17, 12)),)
#经过bar()转换后就是{'network': '农林牧渔', 'service': '172.16.1.6'}
#field是选取哪些字段的数据要返回给前端
def bar(ret,sqlname,field=None,user_id=None):
    for item in ret:
        if sqlname in ('phone1','phone2'):
            temp_item = item[1]
            temp_item = temp_item.strftime(('%Y-%m-%d %X'))
            obj = foo(to_phone=item, recv_time=temp_item, user_id=item[2],smg_code=item[3], 行业端口=item[4], report_code=item[5],
                      status=item[6], msg=item[7], subret_code=item[8])
        elif sqlname == 'phone3':
            obj = foo(user_id=item, datestr=item[1], status=item[2], sum=item[3])
        elif sqlname in ('success1','success2','success3'):
            # obj = foo(smg_code=item, user_id=item[1], datestr=item[2], num=item[3], success=item[4], audit=item[5],
            #           commit_success=item[6],
            #           commit_fail=item[7], subret_success=item[8], subret_fail=item[9], report_success=item[10],
            #           report_fail=item[11], black_template=item[12])
            obj = foo(smg_code=item, user_id=item[1], 日期=item[2], 总条数=item[3], 成功率=item[4], 审计中2=item[5],
                      提交成功3=item[6],
                      提交失败4=item[7], 应答成功5=item[8], 应答失败6=item[9], 状态报告成功7=item[10],
                      状态报告失败8=item[11], 黑名单12=item[12])
        elif sqlname == '省份通道成功率':
            obj = foo(smg_code=item, user_id=item[1], province=item[2], num=item[3], audit=item[4],
                      commit_success=item[5],
                      commit_fail=item[6], subret_success=item[7], subret_fail=item[8], report_success=item[9],
                      report_fail=item[10], black_template=item[11])
        elif sqlname == '回执率':
            obj = foo(sum=item, recvtime=item[1], second10=item[2], second20=item[3], second30=item[4],
                      second60=item[5],
                      second120=item[6],
                      secondet120=item[7], fail=item[8])
        elif sqlname in ('1分钟内','send1'):
            temp_item = item[1]
            temp_item = temp_item.strftime(('%Y-%m-%d %X'))
            obj = foo(smg_code=item, send_time=temp_item, sum=item[2])
        elif sqlname == '通道统计':
            obj = foo(smg_code=item, report_code=item[1], sum=item[2])
        elif sqlname == '用户统计':
            obj = foo(user_id=item, report_code=item[1], sum=item[2])
        elif sqlname == '内容规律':
            obj = foo(user_id=item, status=item[1], content=item[2], sum=item[3])
        elif sqlname == 'osp重推表':
            obj = foo(stat=item, userid=item[1], sum=item[2])
        elif sqlname == 'sgip重推表':
            obj = foo(create_time=item.strftime(('%Y-%m-%d %X')), fail_count=item[1], recv_time=item[2].strftime(('%Y-%m-%d %X')), to_phone=item[3], user_id=item[4],
                      smg_code=item[5],report_time=item[6].strftime(('%Y-%m-%d %X')), report_code=item[7], status=item[8], content=item[9])
        elif sqlname in ('device','check1'):
            print('-->item',item)
            obj = foo(project=item[0], aliasname=item[1], offlinetime=item[2],point=item[3])
        elif sqlname == 'online':
            print('-->item',item)
            obj = foo(project=item[0], devices=item[1], ontheline=item[2],offline=item[3],rate=item[4],create_time=item[5])
        elif sqlname == 'state':
            print('-->item',item)
            obj = foo(project=item[0], sn=item[1], voltage=item[2],battery=item[3],temperature=item[4],
                      signal_4g=item[5],
                      version=item[6],
                      create_time=item[7])
        elif sqlname == 'inspect':
            print('-->item',item)
            obj = foo(project=item[0], ip=item[1], cpu=item[2],mem=item[3],disk=item[4],
                      vda1=item[5],
                      vdb1=item[6],
                      network=item[7],
                      service=item[8],
                      url=item[9],
                      create_time=item[10])
        elif sqlname == 'check2':
            obj = foo(phone=item, create_time=item[1].strftime(('%Y-%m-%d %X')),
                      last_send_time=item[2].strftime(('%Y-%m-%d %X')),check_time=item[3].strftime(('%Y-%m-%d %X')), content=item[4])
        elif sqlname in ('default','area'):
            obj = {}
            for i, v in enumerate(field):
                obj[v] = item[i]
                if sqlname == 'area':
                    obj['name'] = item[-2]
                obj['count'] = item[-1]
        elif sqlname == '多个签名':
            # if len(user_id) > 2:
            obj = foo(user_id_or_smg_code=item, sign=item[1], sum=item[2])
        elif sqlname == '单个签名':
            print('单个签名',ret)
            if len(ret) == 2:
                obj = foo(content=item, sum=item[1])
            else:
                obj = foo(user_id=item,content=item[1], sum=item[2])
        elif sqlname == 'nop1':
            print('========================')
            obj = foo(机构ID=item, 日期=item[1], 总条数=item[2], 未能发送0=item[3],
                      未知状态1=item[4],
                      明确成功3=item[5],
                      明确失败4=item[6], 审核拦截5=item[7],网关拦截7=item[8],超时9=item[9])
        elif sqlname == 'nop2':
            obj = foo(org_id=item, to_mobile=item[1], presend_time=item[2].strftime(('%Y-%m-%d %X')),msg_content=item[3],report=item[4])
        elif sqlname == 'nop3':
            obj = foo(机构ID=item, full_name=item[1], 日期=item[2], 总条数=item[3], 未能发送0=item[4],
                      未知状态1=item[5],
                      明确成功3=item[6],
                      明确失败4=item[7], 审核拦截5=item[8],网关拦截7=item[9],超时9=item[10])
        elif sqlname == '黑模板':
            obj = foo(template_no=item, content=item[1], is_black=item[2])
        elif sqlname == 'subplatform1':
            obj = foo(user_id=item, 日期=item[1], 总条数=item[2], 审计中2=item[3],
                      提交成功3=item[4],
                      提交失败4=item[5], 应答成功5=item[6], 应答失败6=item[7], 状态报告成功7=item[8],
                      状态报告失败8=item[9],费用不足=item[10],黑名单12=item[11],非法关键字13=item[12],审计未通过14=item[13],
                      其他15=item[14],白名单16=item[15],无效的短信长度18=item[16],无效签名19=item[17])
        elif sqlname in 'subplatform2':
            obj = foo(smg_code=item, 日期=item[1], 总条数=item[2], 审计中2=item[3],
                      提交成功3=item[4],
                      提交失败4=item[5], 应答成功5=item[6], 应答失败6=item[7], 状态报告成功7=item[8],
                      状态报告失败8=item[9],费用不足=item[10],黑名单12=item[11],非法关键字13=item[12],审计未通过14=item[13],
                      其他15=item[14],白名单16=item[15],无效的短信长度18=item[16],无效签名19=item[17])
        elif sqlname in 'subplatform3':
            if len(ret) == 21:
                obj = foo(user_id=item, fee=item[1], corp_name=item[2], salesman=item[3],
                          日期=item[4], 总条数=item[5],
                          审计中2=item[6], 提交成功3=item[7],
                          提交失败4=item[8], 应答成功5=item[9], 应答失败6=item[10], 状态报告成功7=item[11],
                          状态报告失败8=item[12], 费用不足=item[13], 黑名单12=item[14], 非法关键字13=item[15], 审计未通过14=item[16],
                          其他15=item[17], 白名单16=item[18], 无效的短信长度18=item[19], 无效签名19=item[20])
            else:
                obj = foo(smg_code=item,user_id=item[1],fee=item[2],corp_name=item[3],salesman=item[4], 日期=item[5], 总条数=item[6],
                          审计中2=item[7],提交成功3=item[8],
                          提交失败4=item[9], 应答成功5=item[10], 应答失败6=item[11], 状态报告成功7=item[12],
                          状态报告失败8=item[13],费用不足=item[14],黑名单12=item[15],非法关键字13=item[16],审计未通过14=item[17],
                          其他15=item[18],白名单16=item[19],无效的短信长度18=item[20],无效签名19=item[21])
        elif sqlname in 'subplatform4':
            if len(ret) == 21:
                obj = foo(user_id=item, corp_name=item[1], employee_name=item[2], fee=item[3],
                          日期=item[4], 总条数=item[5],
                          审计中2=item[6], 提交成功3=item[7],
                          提交失败4=item[8], 应答成功5=item[9], 应答失败6=item[10], 状态报告成功7=item[11],
                          状态报告失败8=item[12], 费用不足=item[13], 黑名单12=item[14], 非法关键字13=item[15], 审计未通过14=item[16],
                          其他15=item[17], 白名单16=item[18], 无效的短信长度18=item[19], 无效签名19=item[20])
            else:
                obj = foo(smg_code=item,user_id=item[1],corp_name=item[2],employee_name=item[3],fee=item[4], 日期=item[5], 总条数=item[6],
                          审计中2=item[7],提交成功3=item[8],
                          提交失败4=item[9], 应答成功5=item[10], 应答失败6=item[11], 状态报告成功7=item[12],
                          状态报告失败8=item[13],费用不足=item[14],黑名单12=item[15],非法关键字13=item[16],审计未通过14=item[17],
                          其他15=item[18],白名单16=item[19],无效的短信长度18=item[20],无效签名19=item[21])
        elif sqlname in 'xinyunxin1':
            # temp_item = item[1]
            # temp_item = temp_item.strftime(('%Y-%m-%d %X'))
            obj = foo(cust_no=item, 日期=item[1].strftime(('%Y-%m-%d %X')), 总条数=item[2], 审计中2=item[3],
                      提交成功3=item[4],
                      提交失败4=item[5], 应答成功5=item[6], 应答失败6=item[7], 状态报告成功7=item[8],
                      状态报告失败8=item[9],费用不足=item[10],黑名单12=item[11],非法关键字13=item[12],审计未通过14=item[13],
                      其他15=item[14],白名单16=item[15],无效的短信长度18=item[16],无效签名19=item[17])
        elif sqlname in 'xinyunxin2':
            obj = foo(platform_num=item, 总条数=item[1], 审计中2=item[2],
                      提交成功3=item[3],
                      提交失败4=item[4], 应答成功5=item[5], 应答失败6=item[6], 状态报告成功7=item[7],
                      状态报告失败8=item[8],费用不足=item[9],黑名单12=item[10],非法关键字13=item[11],审计未通过14=item[12],
                      其他15=item[13],白名单16=item[14],无效的短信长度18=item[15],无效签名19=item[16])
        yield obj
class get_messages(APIView):
    def post(self, request, *args, **kwargs):
        data = json.loads(request.body.decode('utf-8'))
        db = data['db']
        print('-->data',data)
        if not db:
            return JsonResponse({'code':0,'msg':"请选择数据库"})
        if db == 'lt1':
            conn = ('192.168.2.1','sms','gstgate;Normal;sms')
        elif db == 'lt2':
            conn = ('192.168.2.6','sms2','gstgate;Normal;sms2')
        elif db == 'lt9':
            conn = ('192.168.3.2','sms2','gstgate;Normal;sms2')
        elif db == 'yd3':
            conn = ('172.16.102.3','osp','gstsms;Normal;osp')
        elif db == 'dx4':
            conn = ('192.168.1.4','dxsp','gstgate;Normal;dxsp')
        start = "'" + data['timerange'][0] + "'"
        end = "'" + data['timerange'][1] + "'"
        network = data['network']
        # check_person = "'" + data['check_person'] + "'"
        # county = "'" + data['county'] + "'"
        # aliasname = "'" + data['aliasname'] + "'"
        # version = "'" + data['version'] + "'"
        service = data['service']
        org_id = "'" + data['org_id'] + "'"
        status = data['status']
        report_code = "'" + data['report_code'] + "'"
        province = "'" + data['province'] + "'"
        phone = "'" + data['phone'] + "'"
        signature = "'" + data['signature'] + "'"
        cust_no = "'" + data['cust_no'] + "'"
        hh = "'" + data['hh'] + "'"
        mi = "'" + data['mi'] + "'"
        field = None
        areaLabel = []
        inputLabel = []
        # print('-->check_person', check_person)
        print('-->data sql', data['sql'])
        # print('-->user_id', user_id)
        sqlname = data['sql']

        #check1语句，单个条件
        # if sqlname in ('check1','default','area'):
        #     areaLabel = []
        #     field = []
        #     condition = []
        #     field_dict = {'a.smg_code': smg_code, 'a.user_id': user_id, 'aliasname': data['aliasname'], 'status': status}
        #     print('-->field_dict 111111111111111111', field_dict)
        #     value = data['aliasname']
        #     if len(value) > 2 or value.isnumeric():
        #         print('-->value 111111111111111111', value,len(value))
        #         if value == 'all':
        #             pass
        #         else:
        #             condition.append("a.aliasName like '%{0}%'".format(value))
        #             print('-->condition',condition)
        #     if condition:
        #         sql = get_sql(sqlname).format(db.replace("'", ''),aliasname,start,end).replace('tempstr',condition[0])
        #         print('-->sqlcheck1',sql)
        #     else:
        #         sql = get_sql(sqlname).format(db.replace("'", ''), '', start, end).replace('and tempstr\n','')

        #default语句，多个条件
        # if sqlname in ('inspect'):
        #     inputLabel = []
        #     field = []
        #     condition = []
        #     field_dict = {'network': network, 'service': service}
        #     print('-->field_dict 111111111111111111', field_dict)
        #     print(len(data['report_code']))
        #     for key, value in field_dict.items():
        #         print('len(value)',len(value))
        #         # if len(value) > 1:
        #         print('-->value 111111111111111111', value, len(value))
        #         field.append(key)
        #         #多个字段类型，inputLabel返回给前端，
        #         # inputLabel.append({'prop': key, 'label': key, 'width': 330})
        #         print('-->inputLabel 111111111111111111', inputLabel)
        #         # if key == 'network':
        #         #     print('-->network1 value 111111111111111111', value)
        #         #     if value == 'all':
        #         #         pass
        #         #     else:
        #         #         print('-->network value 111111111111111111', value)
        #         condition.append("{0} like '%{1}%' and".format(key, value))
        #         # else:
        #         #     if value in ("'all'", 'all',''):
        #         #         pass
        #         #     else:
        #         #         condition.append("{0} like '%{1}%' and".format(key, value))
        #         # elif key == 'service' and 1 <= len(value) < 3:
        #         #     return JsonResponse({'code': 0, 'msg': "输入长度小于3"})
        #     if sqlname == 'inspect':
        #         print('get_sql(sqlname)',get_sql(sqlname).format(','.join(field), start, end))
        #         print('condition',condition)
        #         sql = get_sql(sqlname).format(','.join(field), start, end).replace('where','where {0}').format(' '.join(condition))
        if sqlname == 'inspect':
            sql = get_sql(sqlname).format(db.replace("'", ''))
        elif sqlname in ('sgip重推表','phone1'):
            sql = get_sql(sqlname).format(phone)
        elif sqlname in ('phone2'):
            sql = get_sql(sqlname).format(start,end,phone)
        elif sqlname == 'device':
            print('db111',db)
            sql = get_sql(sqlname).format(db.replace("'", ''),start,end)
        elif sqlname == 'online':
            sql = get_sql(sqlname).format(db.replace("'", ''))
        elif sqlname == 'state':
            sql = get_sql(sqlname).format(db.replace("'", ''))
        elif sqlname == 'inspect':
            sql = get_sql(sqlname).format(db.replace("'", ''))
        # elif sqlname == 'check1':
        #     sql = get_sql(sqlname).format("'"+db+"'",'',start,end)
        elif sqlname == '用户统计' or sqlname == 'subplatform1':
            sql = get_sql(sqlname).format(user_id,start,end)
        elif sqlname == '多个签名':
            if len(user_id) > 2:
                sql = get_sql(sqlname).format(user_id,start,end)
            else:
                sql = get_sql(sqlname).format(smg_code,start,end).replace('user_id','smg_code')
        elif sqlname in ('内容规律','phone3'):
            sql = get_sql(sqlname).format(user_id,status,start,end)
        elif sqlname in ('nop1','nop2'):
            sql = get_sql(sqlname).format(org_id,start,end)
        elif sqlname in ('nop3'):
            if len(org_id) < 3 and len(hh) > 3:
                sql = get_sql(sqlname).format(start,end).replace("s' )","s') and to_char(a.PRESEND_TIME,'hh24:mi') BETWEEN {0} and {1}".format(hh,mi))
            elif len(org_id) > 3 and len(hh) < 3:
                sql = get_sql(sqlname).format(start, end).replace('b.org_id','b.org_id and a.org_id = {0}'.format(org_id))
            else:
                sql = get_sql(sqlname).format(start, end).replace('b.org_id','b.org_id and a.org_id = {0}'.format(org_id)).replace("s' )","s') and to_char(a.PRESEND_TIME,'hh24:mi') BETWEEN {0} and {1}".format(hh,mi))
        elif sqlname in ('subplatform3'):
            print('hh24',len(hh))
            if len(smg_code) < 3 and len(hh) > 3:
                sql = get_sql(sqlname).format(start, end).replace('t2.smg_code,','').replace('))',")) and to_char(t2.RECV_TIME,'hh24:mi') BETWEEN {0} and {1}").format(hh,mi)
            elif len(smg_code) > 3 and len(hh) < 3:
                sql = get_sql(sqlname).format(start,end).replace('SEND_SEQ_ID','SEND_SEQ_ID and t2.smg_code = {0}').format(smg_code)
            else:
                sql = get_sql(sqlname).format(start, end).replace('SEND_SEQ_ID','SEND_SEQ_ID and t2.smg_code = {0}').format(smg_code).replace('))',")) and to_char(t2.RECV_TIME,'hh24:mi') BETWEEN {0} and {1}").format(hh,mi)
        elif sqlname in ('subplatform4'):
            if len(smg_code) < 3 and len(hh) > 3:
                sql = get_sql(sqlname).format(start, end).replace('a.smg_code,','').replace('smg_code,','').replace('))',")) and to_char(B.RECV_TIME,'hh24:mi') BETWEEN {0} and {1}").format(hh,mi)
            elif len(smg_code) > 3 and len(hh) < 3:
                sql = get_sql(sqlname).format(start,end).replace('SEND_SEQ_ID','SEND_SEQ_ID and a.smg_code = {0}').format(smg_code)
            else:
                sql = get_sql(sqlname).format(start, end).replace('SEND_SEQ_ID','SEND_SEQ_ID and a.smg_code = {0}').format(smg_code).replace('))',")) and to_char(B.RECV_TIME,'hh24:mi') BETWEEN {0} and {1}").format(hh,mi)
            # if db in ('yd5','yd8','yd12','yd13'):
            print('db',db)
            if db in ('yd6','yd10'):
                sql = sql.replace('yduc','ucenter')
            elif db in ('dx4','dx7','dx11'):
                sql = sql.replace('yduc','dxuc')
        elif sqlname == 'xinyunxin1':
            sql = get_sql(sqlname).format(cust_no,start,end)
        elif sqlname == '黑模板':
            sql = 'SELECT TEMPLATE_NO,VOICECONTENT,IS_BLACK FROM OP_USER_TEMPLATE WHERE VOICECONTENT LIKE "%【{0}】%"'.format(signature).replace("'",'')
            sql = sql.replace('"',"'").encode('utf-8')
            print('sql llllllllllllllllllllllllllllll')
        elif sqlname == '单个签名':
            if len(user_id) > 2:
                sql = sql = get_sql(sqlname).format(signature.replace("'",''),start,end).replace('))', ")) and user_id = {0}").format(user_id)
            else:
                sql = get_sql(sqlname).format(signature.replace("'", ''), start, end).replace('user_id,', '')
        elif sqlname == '省份通道成功率':
            if db.startswith('lt'):
                sub_phone = 3
            else:
                sub_phone = 1
            sql = get_sql(sqlname).format(sub_phone,smg_code,province,start,end)
        elif sqlname == 'success2':
            sql = get_sql('success1').replace('SEND_SEQ_ID','SEND_SEQ_ID and a.user_id = {2}').format(start,end,user_id)
        elif sqlname == 'success3':
            sql = get_sql('success1').replace('SEND_SEQ_ID','SEND_SEQ_ID and a.smg_code = {2}').format(start,end,smg_code)
        else:
            sql = get_sql(sqlname).format(start,end)
        print('-->db', db)
        # if db != 'localhost':
        #     return JsonResponse({'code': 0, 'msg': '只允许localhost进行连接'})
        if db.startswith('lt'):
            print('lt 1111111111')
            if isinstance(sql,str):
                sql = sql.replace('osp', 'g_sgip')
                if sqlname in ('phone1','phone2'):
                    sql = sql.replace('REAL_SRC_ID','REAL_SP_NUMBER')
            else:
                sql = sql.decode('utf-8').replace('osp', 'g_sgip')
            # if sqlname in ('10秒内','check1'):
            #     ql = sql.replace('osp', 'g_sgip')
        print('-->sql',sql)
        import pymysql
        try:
            print('1111111111111111111111111')
            try:
                # ret = Oracle(conn).fetchall(x)

                # 不过堡垒机
                # conn = cx_Oracle.connect('cmgw/g9%iX3oA#stjsGQ#@192.168.6.38:1521/gstgate')
                # cur = conn.cursor()
                # cur.execute(sql)
                # print(cur.fetchall())
                dbparam = {
                    'host': '127.0.0.1',
                    'port': 3306,
                    'user': 'inspect',
                    'password': 'yKRL6x9i!Qr1]mJF',
                    'database': 'alerts',
                    'charset': 'utf8'
                }
                # 在函数调用时两个**代表关键字参数解包操作
                xconn = pymysql.connect(**dbparam)
                # 创建一个游标
                cursor = xconn.cursor()
                # sql = 'insert into rate (county,rate) values(%s,%s)'
                cursor.execute(sql)
                ret = cursor.fetchall()
                cursor.close()
                xconn.close()
                # with SSHTunnelForwarder(
                #         ('jp.33de9.com.cn', 60022),
                #         ssh_username="ssw",
                #         ssh_password="a10241010",
                #         remote_bind_address=(conn, 1521)) as server:
                #     print(111)
                #     conn = (conn[1], '123456', '127.0.0.1:%d/%s' % (server.local_bind_port,conn[2]))
                #     print('tmp conn', conn)
                #     xconn = Connection(*conn)
                #     cursor = xconn.cursor()  # 新建游标
                #     cursor.execute(sql)  # 执行sql语句
                #     ret = cursor.fetchall()
                #     cursor.close()
                #     xconn.close()
            except Exception as e:
                print('e', e)
                f_sql = open(r'D:\桌面\sql1.txt','w',encoding='utf-8')
                f_sql.write(str(e))
                f_sql.close()
                return JsonResponse({'code':0,'msg':"获取数据异常，具体错误："+str(e)})
            print('-->ret',ret)
            if not ret:
                return JsonResponse({'code': 0, 'msg': '无数据'})
            # for i in ret:
            #     if (isinstance(i[1],datetime.datetime)):
            #         i[1] = i[1].strftime(('%Y-%m-%d %X'))
            data = list(bar(ret,sqlname,field))
            print('-->after excute data',data)
            commons.FileHandler().dump(data,db)
            if inputLabel:
                return JsonResponse({'code': 1, 'data': data,'inputLabel':inputLabel})
            return JsonResponse({'code':1,'data':data})
        except Exception as e:
            return JsonResponse({'code':0,'msg':"获取手机数据异常，具体错误："+str(e)})

class get_urls(APIView):
    def get(request,*args,**kwargs):
        education_file = 'education_server_%s.xlsx' % datetime.datetime.now().__format__('%m-%d')
        yzd_file = 'yzd_server_%s.xlsx' % datetime.datetime.now().__format__('%m-%d')
        medical_file = 'medial_server_%s.xlsx' % datetime.datetime.now().__format__('%m-%d')
        #拷贝教育巡检数据
        cp_file.scp_by_key('172.16.150.28', 22,'/home/logger/%s' % education_file,'/tmp/%s' % education_file,'logger','/root/.ssh/id_rsa_172.16.150.28')
        #拷贝优智多巡检数据
        cp_file.scp_by_key('172.16.44.177', 22,'/home/logger/%s' % yzd_file,'/tmp/%s' % yzd_file,'logger','/root/.ssh/id_rsa_172.16.44.177')
        #拷贝医疗巡检数据
        os.system('cur_date="`date +%m-%d`";scp -i /root/.ssh/id_rsa_47.96.84.110 admin@47.96.84.110:/home/admin/medical_server_${cur_date}.xlsx /tmp/')
        
        #导入优智多数据到weekly_check表
        yzdState = yzd.main()
        time.sleep(1)
        #导入教育数据到weekly_check表
        eduState = edu.main()
        time.sleep(1)
        #导入医疗数据到weekly_check表
        medicalState = medical.main()
        print('-->urlsState',yzdState,eduState,medicalState)
        inspect_state = [yzdState,eduState,medicalState]

        cities = ['优智多数据', '教育数据', '医疗数据']
        return JsonResponse({'data': {
            'urlsData':{'name':'巡检数据导入','urlsState':inspect_state,'cities':cities},
        }})

class getAlertsView(APIView):
    authentication_classes = []
    def get(request,*args,**kwargs):
        sql = 'SELECT * from alerts1170'
        res = conn_mysql.conn_mysql(sql)[0]
        print('get_alerts res',res)

        red_alerts = res[1].split(',')
        if red_alerts:
            rlen = len(red_alerts)
        else:
            rlen = 0
        orange_alerts = res[2].split(',')
        if orange_alerts:
            olen = len(orange_alerts)
        else:
            olen = 0
        yellow_alerts = res[3].split(',')
        if yellow_alerts[0]:
            ylen = len(yellow_alerts)
        else:
            ylen = 0
        blue_alerts = res[4].split(',')
        if blue_alerts:
            blen = len(blue_alerts)
        else:
            blen = 0
        return JsonResponse({'data': {
            'redAlerts':red_alerts,
            'redAlertsLen': rlen,
            'orangeAlerts': orange_alerts,
            'orangeAlertsLen': olen,
            'yellowAlerts': yellow_alerts,
            'yellowAlertsLen': ylen,
            'blueAlerts': blue_alerts,
            'blueAlertsLen': blen
        }})

class getAlerts2View(APIView):
    authentication_classes = []
    def get(request,*args,**kwargs):
        sql = 'SELECT * from alerts1170'
        res = conn_mysql.conn_mysql(sql)[0]
        print('get_alerts2 res',res)

        red_alerts = res[1].split(',')
        if red_alerts:
            rlen = len(red_alerts)
        else:
            rlen = 0
        orange_alerts = res[2].split(',')
        if orange_alerts:
            olen = len(orange_alerts)
        else:
            olen = 0
        yellow_alerts = res[3].split(',')
        if yellow_alerts[0]:
            ylen = len(yellow_alerts)
        else:
            ylen = 0
        blue_alerts = res[4].split(',')
        if blue_alerts:
            blen = len(blue_alerts)
        else:
            blen = 0
        return JsonResponse({'data': {
            'redAlerts':red_alerts,
            'redAlertsLen': rlen,
            'orangeAlerts': orange_alerts,
            'orangeAlertsLen': olen,
            'yellowAlerts': yellow_alerts,
            'yellowAlertsLen': ylen,
            'blueAlerts': blue_alerts,
            'blueAlertsLen': blen
        }})

class listAlertsView(APIView):
    authentication_classes = []
    def get(request,*args,**kwargs):
        sql = 'SELECT * from alerts1170'
        res = conn_mysql.conn_mysql(sql)[0]
        print('get_alerts res',res)

        red_alerts = res[1].split(',')
        orange_alerts = res[2].split(',')
        yellow_alerts = res[3].split(',')
        blue_alerts = res[4].split(',')
        return JsonResponse({'data': {
            'redAlerts':red_alerts,
            'orangeAlerts': orange_alerts,
            'yellowAlerts': yellow_alerts,
            'blueAlerts': blue_alerts,
        }})

class listAlertsView(APIView):
    authentication_classes = []
    def get(request,*args,**kwargs):
        sql = 'SELECT * from alerts1170'
        res = conn_mysql.conn_mysql(sql)[0]
        print('get_alerts res',res)

        red_alerts = res[1].split(',')
        orange_alerts = res[2].split(',')
        yellow_alerts = res[3].split(',')
        blue_alerts = res[4].split(',')
        return JsonResponse({'data': {
            'redAlerts':red_alerts,
            'orangeAlerts': orange_alerts,
            'yellowAlerts': yellow_alerts,
            'blueAlerts': blue_alerts,
        }})

class listAlerts3View(APIView):
    authentication_classes = []
    def get(request,*args,**kwargs):
        sql = 'SELECT county，red_alerts,orange_alerts,yellow_alerts,blue_alerts from alerts1170'
        res = conn_mysql.conn_mysql(sql)

        red = []
        orange = []
        yellow = []
        blue = []

        for item in res:
            if ('娄底' in item[0]):
                ld = item
            elif ('长沙' in item[0]):
                css = item
            elif ('邵阳' in item[0]):
                sys = item
            elif ('益阳' in item[0]):
                yys = item
            elif ('张家界' in item[0]):
                zjjs = item
            elif ('湘西州' in item[0]):
                xxz = item
            elif ('常德' in item[0]):
                cds = item
            elif ('株洲' in item[0]):
                zzs = item
        res2 = (ld,css,sys,yys,zjjs,xxz,cds,zzs)

        print('alerts3',res2)
        def get_len(item, color, num):
            if item[num]:
                color.append(len(item[num].split('<br><br>')))
            else:
                color.append('')

        for item in res:
            print(item)
            if ('娄底' in item[0]):
                get_len(item[1:], red, 0)
            get_len(item, orange, 1)
            get_len(item, yellow, 2)
            get_len(item, blue, 3)
        return JsonResponse({'data': {
            'red':red,
            'orange': orange,
            'yellow': yellow,
            'blue': blue,
        }})

def get_alerts_from_db(type=None):
    sql = 'SELECT county,red_alerts,orange_alerts,yellow_alerts,blue_alerts from alerts1170'
    res = conn_mysql.conn_mysql(sql)
    print('get_alerts_from_db res',res)
    for item in res:
        if ('娄底' in item[0]):
            if type == 'bar':
                ld = item[1:]
            else:
                ld = item
        elif ('长沙' in item[0]):
            if type == 'bar':
                css = item[1:]
            else:
                css = item
        elif ('邵阳' in item[0]):
            if type == 'bar':
                sys = item[1:]
            else:
                sys = item
        elif ('益阳' in item[0]):
            if type == 'bar':
                yys = item[1:]
            else:
                yys = item
        elif ('张家界' in item[0]):
            if type == 'bar':
                zjjs = item[1:]
            else:
                zjjs = item
        elif ('湘西州' in item[0]):
            if type == 'bar':
                xxz = item[1:]
            else:
                xxz = item
        elif ('常德' in item[0]):
            if type == 'bar':
                cds = item[1:]
            else:
                cds = item
        elif ('株洲' in item[0]):
            if type == 'bar':
                zzs = item[1:]
            else:
                zzs = item
    return (ld,css,sys,yys,zjjs,xxz,cds,zzs)

def get_alerts4_from_db():
    import pysnooper
    sql = 'SELECT county,red_alerts,orange_alerts,yellow_alerts,blue_alerts from sf1170'
    # sql = "SELECT county,json_extract(red_alerts,'$[*]'),json_extract(orange_alerts,'$[*]'),json_extract(yellow_alerts,'$[*]'),json_extract(blue_alerts,'$[*]') from sf1170"
    # sql = "SELECT county,json_extract(red_alerts,'$[*].point'),json_extract(red_alerts,'$[*].content'),json_extract(orange_alerts,'$[*].point'),json_extract(orange_alerts,'$[*].content'),json_extract(yellow_alerts,'$[*].point'),json_extract(yellow_alerts,'$[*].content'),json_extract(blue_alerts,'$[*].point'),json_extract(blue_alerts,'$[*].content') from sf1170"
    res = conn_mysql.conn_mysql(sql)
    print('get_alerts4_from_db res',res)
    cds_list,ld_list,sys_list,css_list,zjjs_list,zzs_list,yys_list,xxz_list = [],[],[],[],[],[],[],[]
    for k, v in enumerate(res):
        if ('常德' in res[k][0]):
            cds_list.append(res[k][0])
            for i in res[k][1:]:
                cds_list.append(json.loads(i))
        elif ('娄底' in res[k][0]):
            ld_list.append(res[k][0])
            for i in res[k][1:]:
                ld_list.append(json.loads(i))
        elif ('邵阳' in res[k][0]):
            sys_list.append(res[k][0])
            for i in res[k][1:]:
                sys_list.append(json.loads(i))
        elif ('湘西' in res[k][0]):
            xxz_list.append(res[k][0])
            for i in res[k][1:]:
                xxz_list.append(json.loads(i))
        elif ('张家界' in res[k][0]):
            zjjs_list.append(res[k][0])
            for i in res[k][1:]:
                zjjs_list.append(json.loads(i))
        elif ('株洲' in res[k][0]):
            zzs_list.append(res[k][0])
            for i in res[k][1:]:
                zzs_list.append(json.loads(i))
        elif ('益阳' in res[k][0]):
            yys_list.append(res[k][0])
            for i in res[k][1:]:
                yys_list.append(json.loads(i))
        elif ('长沙' in res[k][0]):
            css_list.append(res[k][0])
            for i in res[k][1:]:
                css_list.append(json.loads(i))
    ld = ld_list[1:]
    print('cds111')
    with pysnooper.snoop():
        cds = cds_list[1:]
        sys = sys_list[1:]
        xxz = xxz_list[1:]
        zjjs = zjjs_list[1:]
        zzs = zzs_list[1:]
        yys = yys_list[1:]
        css = css_list[1:]

    return {'cds':cds,'ld':ld,'sys':sys,'xxz':xxz,'zjjs':zjjs,'zzs':zzs,'yys':yys,'css':css}

def get_rains_from_db():
    sql = 'SELECT city,rain from rains'
    res = conn_mysql.conn_mysql(sql)
    print('get_rains_from_db res',res)
    return res

def get_rate_from_db():
    sql = 'SELECT county,rate from rate'
    res = conn_mysql.conn_mysql(sql)
    print('get_rate_from_db res',res)
    return res

class listAlerts2View(APIView):
    authentication_classes = []
    def get(request,*args,**kwargs):


        red = []
        orange = []
        yellow = []
        blue = []


        res2 = get_alerts_from_db('bar')

        def get_len(item, color, num):
            if item[num]:
                color.append(len(item[num].split('<br><br>')))
            else:
                color.append('')

        for item in res2:
            print(item)
            get_len(item, red, 0)
            get_len(item, orange, 1)
            get_len(item, yellow, 2)
            get_len(item, blue, 3)
        return JsonResponse({'data': {
            'red':red,
            'orange': orange,
            'yellow': yellow,
            'blue': blue,
        }})

class AlertView(APIView):
    authentication_classes = []
    def get(self, request, *args, **kwargs):
        return HttpResponse(content=open("./templates/index.html",encoding='utf-8').read())

class Alert2View(APIView):
    authentication_classes = []
    def get(self, request, *args, **kwargs):
        res = get_alerts_from_db()
        ld, css, sys, yys,zjjs,xxz,cds,zzs = res[0], res[1], res[2], res[3], res[4], res[5], res[6], res[7]
        print('Alert2View res', ld, css)
        return render(request,"index_big.html",{'ld':ld,'css':css,'sys':sys,'yys':yys,'zjjs':zjjs,
                                                'xxz':xxz,'cds':cds,'zzs':zzs})

class Alert3View(APIView):
    authentication_classes = []
    def get(self, request, *args, **kwargs):
        res = get_alerts_from_db()
        ld, css, sys, yys,zjjs,xxz,cds,zzs = res[0], res[1], res[2], res[3], res[4], res[5], res[6], res[7]
        print('Alert3View res', ld, css)
        return JsonResponse({'data': {'ld': ld, 'css': css, 'sys': sys, 'yys': yys, 'zjjs': zjjs,
                                  'xxz': xxz, 'cds': cds, 'zzs': zzs}})

class Alert4View(APIView):
    authentication_classes = []
    def get(self, request, *args, **kwargs):
        res = get_alerts4_from_db()
        cds,ld,sys,xxz,zjjs,zzs,yys,css = res['cds'],res['ld'],res['sys'],res['xxz'],res['zjjs'],res['zzs'],res['yys'],res['css']
        print('Alert4View res', type(cds),cds)
        return JsonResponse({'data': {'cds':cds,'ld':ld,'sys':sys,'xxz':xxz,'zjjs':zjjs,'zzs':zzs,'yys':yys,'css':css}})

class RainView(APIView):
    authentication_classes = []
    def get(self, request, *args, **kwargs):
        res = get_rains_from_db()
        # ld, css, sys, yys,zjjs,xxz,cds,zzs = res[0], res[1], res[2], res[3], res[4], res[5], res[6], res[7]
        for i in res:
            city = i[0].strip()
            print('city',city)
            # rate = i[1]
            if (city == '益阳'):
                yys = i
            elif (city == '永顺'):
                xxz = i
            elif (city == '长沙'):
                css = i
            elif (city == '张家界'):
                zjjs = i
            elif (city == '邵阳市'):
                sys = i
            elif (city == '株洲'):
                zzs = i
            elif (city == '常德'):
                cds = i
            elif (city == '娄底'):
                ld = i
        print('RainView res', ld, css)
        return JsonResponse({'data': {'ld': ld, 'css': css, 'sys': sys, 'yys': yys, 'zjjs': zjjs,
                                  'xxz': xxz, 'cds': cds, 'zzs': zzs}})

class RateView(APIView):
    authentication_classes = []
    def get(self, request, *args, **kwargs):
        res = get_rate_from_db()
        for i in res:
            county = i[0]
            # rate = i[1]
            if (county == '益阳'):
                yys = i
            elif (county == '湘西'):
                xxz = i
            elif (county == '长沙'):
                css = i
            elif (county == '张家界'):
                zjjs = i
            elif (county == '邵阳'):
                sys = i
            elif (county == '株洲'):
                zzs = i
            elif (county == '常德'):
                cds = i
            elif (county == '娄底'):
                ld = i
        # ld, css, sys, yys,zjjs,xxz,cds,zzs = res[7], res[2], res[3], res[0], res[4], res[1], res[6], res[5]
        print('RateView res', ld, css,res)
        return JsonResponse({'data': {'ld': ld, 'css': css, 'sys': sys, 'yys': yys, 'zjjs': zjjs,
                                  'xxz': xxz, 'cds': cds, 'zzs': zzs}})

class listOfflineView(APIView):
    authentication_classes = []
    #各地市按掉线数返回，前端多条曲线
    # def get(request,*args,**kwargs):
    #     sql = 'SELECT * from offline1170'
    #     res = conn_mysql.conn_mysql(sql)
    #     print(res)
    #     print('OfflineView res', res)
    #     day,yys, xxz, css, sys, zjjs, zzs, cds, ld = [],[],[],[],[],[],[],[],[]
    #     temp_str = ''
    #     for item in res:
    #         temp_str = item[1].strftime('%Y-%m-%d %X').split(' ')[0].split('2021-')[1]
    #         day.append(temp_str)
    #         yys.append(item[2])
    #         xxz.append(item[3])
    #         css.append(item[4])
    #         sys.append(item[5])
    #         zjjs.append(item[6])
    #         zzs.append(item[7])
    #         cds.append(item[8])
    #         ld.append(item[9])
    #     print('OfflineView yys',day,yys)
    #     return JsonResponse({'data':{'day':day,'ld':ld,'css':css,'sys':sys,'yys':yys,'zjjs':zjjs,
    #                                             'xxz':xxz,'cds':cds,'zzs':zzs}})

    #返回在线率，前端一条曲线
    def get(request,*args,**kwargs):
        sql = 'SELECT * from offline1170'
        res = conn_mysql.conn_mysql(sql)[0][2:]
        print(res)
        print('OfflineView res', res)
        return JsonResponse({'data':{'online':res}})
class OfflineView(APIView):
    authentication_classes = []
    def get(self, request, *args, **kwargs):

        # yys, xxz, css, sys, zjjs, zzs, cds, ld = res[1], res[2], res[3], res[4], res[5], res[6], res[7],res[8]
        return render(request,"line-marker.html")

def upload(request,*args,**kwargs):
    import re,os
    obj = request.FILES.get('file')
    print('upload obj',obj)
    if obj:
        if re.findall('[()]',obj.name):
            print('-->此处括号')
            msg = '上传失败!文件名不要使用括号等特殊字符!'
            return JsonResponse({"status":1,"msg":msg})
        new_name = commons.get_random_str()
        suffix = os.path.splitext(obj.name)[1]
        file_path = os.path.join(settings.MEDIA_ROOT,new_name + suffix)
        f = open(file_path,'wb')
        for chunk in obj.chunks():
            f.write(chunk)
        f.close
        temp_path = 'media/' + new_name + suffix
        url = settings.PIC_URL + temp_path
        return JsonResponse({'data':{'temp_path':temp_path,'url':url},'meta': {'status':200}})

def all_goods_list(request,*args,**kwargs):
    goods_li = models.Goods.objects.filter(is_delete=False, is_onsale=True).values('goods_id', 'goods_name')

    res = {
        'errno': response_code.IS_SUCCESS,
        'statusCode': 200,
        'errMsg': '数据获取成功',
        'data': {
            'goods_list': list(goods_li)
        }
    }
    return JsonResponse(res)

def bar2(data):
    for item in data:
        row = foo(belong=item['belong'],point=item['point'],deviceName=item['deviceName'],
                  deviceID=item['deviceID'],incidence=item['incidence'],describle=item['describle'])
        yield row

def query_devices(request,*args,**kwargs):
    """查询设备信息"""
    print('query_devices',request.GET.get('query'))
    data = request.GET.get('query')
    try:
        print('query data 1',models.yy1170.objects.filter(Q(point__icontains=data)))
        obj_devices = models.yy1170.objects.filter(Q(county__icontains=data) | Q(point__icontains=data) |
                                              Q(deviceID__icontains=data) | Q(deviceName__icontains=data) |
                                              Q(describle__icontains=data) | Q(proposer__icontains=data) |
                                              Q(incidence__icontains=data) | Q(deal__icontains=data) |
                                              Q(solve_date__icontains=data) | Q(responsible_person__icontains=data) |
                                              Q(question_state__icontains=data) | Q(maintenance_date__icontains=data) |
                                              Q(belong__icontains=data)).values()
        devices = list(obj_devices)
        data = list(bar2(devices))
        print('-->after excute data',data)

        commons.FileHandler().dump(data)
        res = {
            'errno': response_code.IS_SUCCESS,
            'statusCode': 200,
            'errMsg': '数据获取成功',
            'data': {
                'devices': devices,
                'total': len(devices)
            }
        }
        # 返回
        return JsonResponse(res)
    except Exception as e:
        # 如果出现异常，返回
        return JsonResponse({'code': 0, 'msg': "查询学生信息出现异常，具体错误：" + str(e)})

def get_devices(request,*args,**kwargs):
    """获取所有学生的信息"""
    try:
        # 使用ORM获取所有学生信息 并把对象转为字典格式
        obj_devices= models.yy1170.objects.all().values()
        # 把外层的容器转为List
        devices = list(obj_devices)
        res = {
            'errno': response_code.IS_SUCCESS,
            'statusCode': 200,
            'errMsg': '数据获取成功',
            'data': {
                'devices': devices,
            }
        }
        # 返回
        return JsonResponse(res)
    except Exception as e:
        # 如果出现异常，返回
        return JsonResponse({'code': 0, 'msg': "获取学生信息出现异常，具体错误：" + str(e)})
